{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "run real_trade.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import qstock as qs\n",
    "qsdata=qs.realtime_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import real_trade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'资金余额': 7489.63, '可用金额': 1113.1, '可取金额': 1113.1, '股票市值': 10161.0, '总资产': 11274.1}\n",
      "INSERT IGNORE INTO `yxk_realtrade_qry` (`account`, `updatetime`, `cash`, `market_value`, `total_assert`,        `flag`,`unreal_pnl`,`real_pnl`,`total_pnl`,`winrate`,`loserate`,`trade_cost`,`trade_count`) VALUES ('A50549',        CURRENT_TIMESTAMP, '1113.1', '10161.0', '11274.1','calculate', '124.00000000000023', '-0.0', '124.00000000000023',        'nan', 'nan', '8647.0', '5')\n"
     ]
    }
   ],
   "source": [
    "from real_trade import *\n",
    "\n",
    "account='A50549'\n",
    "# real_trade(account)\n",
    "# qsdata=qs.realtime_data()\n",
    "# qsdata.set_index('代码',inplace=True)   \n",
    "# cal_pnl(account,0) \n",
    "# check_order(account)\n",
    "check_pos(account)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'keyboard' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-2-b2277b4bf9ab>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mkeyboard\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msend_keys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'{F5}'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'keyboard' is not defined"
     ]
    }
   ],
   "source": [
    "trader\n",
    "keyboard.send_keys('{F5}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from real_trade import *\n",
    "conn = pymysql.connect(host=MYSQL_HOST_M, user=MYSQL_USER, passwd=MYSQL_PASS, db=MYSQL_DB, port=MYSQL_PORT, charset='utf8')\n",
    "cursor = conn.cursor()  \n",
    "account='123'\n",
    "sql=f\"select * from yxk_realtrade_detail where trade_time> date_sub(curdate(), interval 1000 day) and trade_status='finish' and  account='{account}' order by trade_time desc\"\n",
    "tradedetail= pd.read_sql(sql, con=conn)\n",
    "tradedetail['money_cost']=tradedetail['trade_qty']*tradedetail['trade_price']\n",
    "tradesum=tradedetail.groupby(\"stock_id\").sum()\n",
    "tradesum=tradesum[tradesum['trade_qty']>=0]\n",
    "real_net=tradesum[tradesum['trade_qty']==0]['money_cost'].sum()-tradesum[tradesum['trade_qty']==0]['fee'].sum()\n",
    "tradedetail['buy_money']=tradedetail['money_cost'].map(lambda x:x if x>0 else 0)\n",
    "tradesum1=tradedetail[tradedetail['trade_time']> (datetime.datetime.now() - datetime.timedelta(days = 21))].groupby(\"stock_id\").sum()\n",
    "real_df=tradesum1[tradesum1['trade_qty']==0] \n",
    "real_df['rate']=round(-100*real_df['money_cost']/real_df['buy_money'],2)\n",
    "loserate=(real_df['money_cost']>=100).sum()/len(real_df)\n",
    "winrate=(real_df['money_cost']<=-100).sum()/len(real_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stock_id\n",
       "000048     5.72\n",
       "000066     5.55\n",
       "000333     2.10\n",
       "000400     1.98\n",
       "000425    -0.45\n",
       "          ...  \n",
       "688628    -6.10\n",
       "688677    -2.17\n",
       "688680    -7.39\n",
       "688699    10.27\n",
       "688766     4.35\n",
       "Name: rate, Length: 198, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "real_df['rate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>account</th>\n",
       "      <th>stock_name</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>trade_type</th>\n",
       "      <th>trade_time</th>\n",
       "      <th>trade_price</th>\n",
       "      <th>trade_qty</th>\n",
       "      <th>trade_reason</th>\n",
       "      <th>trade_status</th>\n",
       "      <th>...</th>\n",
       "      <th>signal_time</th>\n",
       "      <th>signal_price</th>\n",
       "      <th>strategy_index</th>\n",
       "      <th>condition_name</th>\n",
       "      <th>trade_id</th>\n",
       "      <th>buy_mul</th>\n",
       "      <th>fee</th>\n",
       "      <th>making_price</th>\n",
       "      <th>money_cost</th>\n",
       "      <th>buy_money</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2276</td>\n",
       "      <td>123</td>\n",
       "      <td>贝达药业</td>\n",
       "      <td>300558</td>\n",
       "      <td>buy</td>\n",
       "      <td>2023-04-07 14:42:04</td>\n",
       "      <td>67.90</td>\n",
       "      <td>600</td>\n",
       "      <td>buy_first</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-04-07 13:43:00</td>\n",
       "      <td>68.40</td>\n",
       "      <td>920.0</td>\n",
       "      <td>C_similar_nike</td>\n",
       "      <td>0</td>\n",
       "      <td>1.03</td>\n",
       "      <td>8.15</td>\n",
       "      <td>67.90</td>\n",
       "      <td>40740.0</td>\n",
       "      <td>40740.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2277</td>\n",
       "      <td>123</td>\n",
       "      <td>益方生物-U</td>\n",
       "      <td>688382</td>\n",
       "      <td>buy</td>\n",
       "      <td>2023-04-07 14:42:04</td>\n",
       "      <td>20.01</td>\n",
       "      <td>1600</td>\n",
       "      <td>buy_first</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-04-07 13:43:00</td>\n",
       "      <td>20.28</td>\n",
       "      <td>1833.0</td>\n",
       "      <td>C_uplimit_drumpbegin</td>\n",
       "      <td>0</td>\n",
       "      <td>0.80</td>\n",
       "      <td>6.40</td>\n",
       "      <td>20.01</td>\n",
       "      <td>32016.0</td>\n",
       "      <td>32016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2269</td>\n",
       "      <td>123</td>\n",
       "      <td>芯海科技</td>\n",
       "      <td>688595</td>\n",
       "      <td>sell</td>\n",
       "      <td>2023-04-07 14:41:12</td>\n",
       "      <td>51.10</td>\n",
       "      <td>-800</td>\n",
       "      <td>sell_all</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-04-04 14:09:16</td>\n",
       "      <td>49.42</td>\n",
       "      <td>1328.0</td>\n",
       "      <td>C_similar_zbqt3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>40.88</td>\n",
       "      <td>51.10</td>\n",
       "      <td>-40880.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2270</td>\n",
       "      <td>123</td>\n",
       "      <td>老板电器</td>\n",
       "      <td>002508</td>\n",
       "      <td>sell</td>\n",
       "      <td>2023-04-07 14:41:12</td>\n",
       "      <td>29.48</td>\n",
       "      <td>-500</td>\n",
       "      <td>sell_all</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-03-31 11:10:07</td>\n",
       "      <td>28.42</td>\n",
       "      <td>969.0</td>\n",
       "      <td>C_similar_nike</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>14.74</td>\n",
       "      <td>29.48</td>\n",
       "      <td>-14740.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2267</td>\n",
       "      <td>123</td>\n",
       "      <td>指南针</td>\n",
       "      <td>300803</td>\n",
       "      <td>sell</td>\n",
       "      <td>2023-04-07 14:41:11</td>\n",
       "      <td>57.95</td>\n",
       "      <td>-700</td>\n",
       "      <td>sell_all</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2023-04-06 09:47:04</td>\n",
       "      <td>59.22</td>\n",
       "      <td>920.0</td>\n",
       "      <td>C_similar_nike</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>40.56</td>\n",
       "      <td>57.95</td>\n",
       "      <td>-40565.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1591</th>\n",
       "      <td>2</td>\n",
       "      <td>123</td>\n",
       "      <td>None</td>\n",
       "      <td>603000</td>\n",
       "      <td>buy</td>\n",
       "      <td>2022-12-25 17:17:18</td>\n",
       "      <td>13.86</td>\n",
       "      <td>400</td>\n",
       "      <td>buy_first</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2022-12-20 10:19:00</td>\n",
       "      <td>15.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.86</td>\n",
       "      <td>5544.0</td>\n",
       "      <td>5544.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1592</th>\n",
       "      <td>3</td>\n",
       "      <td>123</td>\n",
       "      <td>None</td>\n",
       "      <td>688032</td>\n",
       "      <td>buy</td>\n",
       "      <td>2022-12-25 17:17:18</td>\n",
       "      <td>827.00</td>\n",
       "      <td>0</td>\n",
       "      <td>buy_first</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2022-12-16 11:16:00</td>\n",
       "      <td>851.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>827.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1593</th>\n",
       "      <td>4</td>\n",
       "      <td>123</td>\n",
       "      <td>None</td>\n",
       "      <td>000679</td>\n",
       "      <td>buy</td>\n",
       "      <td>2022-12-25 17:17:18</td>\n",
       "      <td>5.80</td>\n",
       "      <td>900</td>\n",
       "      <td>buy_first</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2022-12-15 15:00:00</td>\n",
       "      <td>7.62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.80</td>\n",
       "      <td>5220.0</td>\n",
       "      <td>5220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1594</th>\n",
       "      <td>5</td>\n",
       "      <td>123</td>\n",
       "      <td>None</td>\n",
       "      <td>000721</td>\n",
       "      <td>buy</td>\n",
       "      <td>2022-12-25 17:17:18</td>\n",
       "      <td>14.07</td>\n",
       "      <td>400</td>\n",
       "      <td>buy_first</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2022-12-15 15:00:00</td>\n",
       "      <td>14.07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.07</td>\n",
       "      <td>5628.0</td>\n",
       "      <td>5628.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1595</th>\n",
       "      <td>6</td>\n",
       "      <td>123</td>\n",
       "      <td>None</td>\n",
       "      <td>600246</td>\n",
       "      <td>buy</td>\n",
       "      <td>2022-12-25 17:17:18</td>\n",
       "      <td>6.67</td>\n",
       "      <td>700</td>\n",
       "      <td>buy_first</td>\n",
       "      <td>finish</td>\n",
       "      <td>...</td>\n",
       "      <td>2022-12-22 14:27:00</td>\n",
       "      <td>6.86</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.67</td>\n",
       "      <td>4669.0</td>\n",
       "      <td>4669.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1596 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id account stock_name stock_id trade_type          trade_time  \\\n",
       "0         2276     123       贝达药业   300558        buy 2023-04-07 14:42:04   \n",
       "1         2277     123     益方生物-U   688382        buy 2023-04-07 14:42:04   \n",
       "2         2269     123       芯海科技   688595       sell 2023-04-07 14:41:12   \n",
       "3         2270     123       老板电器   002508       sell 2023-04-07 14:41:12   \n",
       "4         2267     123        指南针   300803       sell 2023-04-07 14:41:11   \n",
       "...        ...     ...        ...      ...        ...                 ...   \n",
       "1591         2     123       None   603000        buy 2022-12-25 17:17:18   \n",
       "1592         3     123       None   688032        buy 2022-12-25 17:17:18   \n",
       "1593         4     123       None   000679        buy 2022-12-25 17:17:18   \n",
       "1594         5     123       None   000721        buy 2022-12-25 17:17:18   \n",
       "1595         6     123       None   600246        buy 2022-12-25 17:17:18   \n",
       "\n",
       "      trade_price  trade_qty trade_reason trade_status  ...  \\\n",
       "0           67.90        600    buy_first       finish  ...   \n",
       "1           20.01       1600    buy_first       finish  ...   \n",
       "2           51.10       -800     sell_all       finish  ...   \n",
       "3           29.48       -500     sell_all       finish  ...   \n",
       "4           57.95       -700     sell_all       finish  ...   \n",
       "...           ...        ...          ...          ...  ...   \n",
       "1591        13.86        400    buy_first       finish  ...   \n",
       "1592       827.00          0    buy_first       finish  ...   \n",
       "1593         5.80        900    buy_first       finish  ...   \n",
       "1594        14.07        400    buy_first       finish  ...   \n",
       "1595         6.67        700    buy_first       finish  ...   \n",
       "\n",
       "             signal_time signal_price strategy_index        condition_name  \\\n",
       "0    2023-04-07 13:43:00        68.40          920.0        C_similar_nike   \n",
       "1    2023-04-07 13:43:00        20.28         1833.0  C_uplimit_drumpbegin   \n",
       "2    2023-04-04 14:09:16        49.42         1328.0       C_similar_zbqt3   \n",
       "3    2023-03-31 11:10:07        28.42          969.0        C_similar_nike   \n",
       "4    2023-04-06 09:47:04        59.22          920.0        C_similar_nike   \n",
       "...                  ...          ...            ...                   ...   \n",
       "1591 2022-12-20 10:19:00        15.25            NaN                  None   \n",
       "1592 2022-12-16 11:16:00       851.80            NaN                  None   \n",
       "1593 2022-12-15 15:00:00         7.62            NaN                  None   \n",
       "1594 2022-12-15 15:00:00        14.07            NaN                  None   \n",
       "1595 2022-12-22 14:27:00         6.86            NaN                  None   \n",
       "\n",
       "      trade_id buy_mul    fee  making_price  money_cost  buy_money  \n",
       "0            0    1.03   8.15         67.90     40740.0    40740.0  \n",
       "1            0    0.80   6.40         20.01     32016.0    32016.0  \n",
       "2            0    0.00  40.88         51.10    -40880.0        0.0  \n",
       "3            0    0.00  14.74         29.48    -14740.0        0.0  \n",
       "4            0    0.00  40.56         57.95    -40565.0        0.0  \n",
       "...        ...     ...    ...           ...         ...        ...  \n",
       "1591      None     NaN    NaN         13.86      5544.0     5544.0  \n",
       "1592      None     NaN    NaN        827.00         0.0        0.0  \n",
       "1593      None     NaN    NaN          5.80      5220.0     5220.0  \n",
       "1594      None     NaN    NaN         14.07      5628.0     5628.0  \n",
       "1595      None     NaN    NaN          6.67      4669.0     4669.0  \n",
       "\n",
       "[1596 rows x 22 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
